/* $Revision: $ $Date: $
 * 
 * Copyright (C) 2009 Robert Stephan
 * 
 * This file is part of *** CaCaO - Card Catalogs Online ***
 *
 * CaCaO is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * CaCao is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package cacao.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Vector;

import cacao.client.CaCaODataService;
import cacao.client.data.CatalogObject;
import cacao.client.data.IndexEntryObject;
import cacao.client.data.ItemDetailsObject;
import cacao.client.data.OrderObject;
import cacao.client.data.SearchResultObject;
import cacao.server.normalize.CaCaONormalizer;
import cacao.server.normalize.CaCaONormalizerFactory;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;


public class CaCaODataServiceImpl extends RemoteServiceServlet implements
CaCaODataService {

	private static final long serialVersionUID = -5093998290716371409L;

	public List<CatalogObject> retrieveCatalogs(){
		Vector<CatalogObject> result = new Vector<CatalogObject>();
		//HttpSession session = this.getThreadLocalRequest().getSession();
		try {
			Properties cacaoProps = CaCaOServer.getInstance().getProperties();
			Connection conn = CaCaOServer.getInstance().getDBConnection();
			PreparedStatement psGetData = conn.prepareStatement(cacaoProps.getProperty("CaCaO.server.db.select.catalogs"));
			ResultSet rs = psGetData.executeQuery();
			while(rs.next()){
				CatalogObject co = new CatalogObject();
				co.setCode(rs.getString(2));
				co.setName(rs.getString(3));
				co.setFirstItemID(rs.getInt(4));
				co.setLastItemID(rs.getInt(5));
				co.setItemIDLength(rs.getInt(6));
				co.setExtension(rs.getString(7));
				co.setOrderenabled(rs.getBoolean(8));
				
				result.add(co);
			}
			rs.close();
			psGetData.close();
			conn.close();

		} catch (Exception e) {
			//session.setAttribute(MatrikelConstants.SESSIONATTR_ERRORMSG_STRING,
			//		"Datenbank: Abrufen der Katalogdaten fehlgeschlagen: " + e.getMessage());
		}
		return result;
	}

	public SearchResultObject search(String catalog, String param){
		SearchResultObject result = new SearchResultObject();
		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		try {
			Connection conn = CaCaOServer.getInstance().getDBConnection();
			PreparedStatement ps = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.select.indexSearch"));
			ps.setString(1, catalog);
			ps.setString(2, CaCaONormalizerFactory.getNormalizer().normalize(param));
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				result.setResult(rs.getString(1));
				result.setImageID(rs.getString(2));
				rs.close();
			}
			else{
				rs.close();
				ps = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.select.indexFirst"));
				ps.setString(1, catalog);
				rs = ps.executeQuery();
				if(rs.next()){
					result.setResult(rs.getString(1));
					result.setImageID(rs.getString(2));
					rs.close();
				}
			}
			ps.close();

			if(result.getResult()!=null){
				CaCaONormalizer normalizer = CaCaONormalizerFactory.getNormalizer();
				ps = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.select.indexPrev"));
				ps.setString(1, catalog);
				ps.setString(2, normalizer.normalize(result.getResult()));
				rs = ps.executeQuery();
				while(rs.next()){
					result.getResultList().add(0, rs.getString(1));
				}
				rs.close();
				ps.close();

				result.getResultList().add(result.getResult());

				ps = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.select.indexNext"));
				ps.setString(1, catalog);
				ps.setString(2, normalizer.normalize(result.getResult()));
				rs = ps.executeQuery();
				while(rs.next()){
					result.getResultList().add(rs.getString(1));
				}
				rs.close();
				ps.close();				
			}
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
			//session.setAttribute(MatrikelConstants.SESSIONATTR_ERRORMSG_STRING,
			//		"Datenbank: Abrufen der Katalogdaten fehlgeschlagen: " + e.getMessage());
		}
		return result;
	}

	public String insertOrUpdateItemDetails(ItemDetailsObject details) {
		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		try {
			Connection conn = CaCaOServer.getInstance().getDBConnection();
			conn.setAutoCommit(false);
			
			PreparedStatement ps  = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.update.item"));
			ps.setString(1, details.getShelfmark()); 
			ps.setString(2, details.getDescription()); 
			ps.setBoolean(3, details.isNoLoan());
			ps.setString(4, details.getId());
			int rows = ps.executeUpdate();
			if(rows==0){
				ps.close();
				//no row could be updated -> do an INSERT
				ps  = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.insert.item"));
				ps.setString(1, details.getId());
				ps.setString(2, details.getShelfmark());
				ps.setString(3, details.getDescription());
				ps.setBoolean(4, details.isNoLoan());
				ps.executeUpdate();
			}
			ps.close();
			conn.commit();
			conn.close();
		} catch (SQLException ex) {
			// Fehler behandeln
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			return ex.getMessage();
		} catch (Exception ex) {
			return ex.getMessage();
		}
		return null;
	}

	public ItemDetailsObject retrieveItemDetails(String id) {
		if(id==null) return null;
		ItemDetailsObject result = new ItemDetailsObject(id);
		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		try {
			Connection conn = CaCaOServer.getInstance().getDBConnection();
			PreparedStatement psGetData = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.select.items"));
			psGetData.setString(1,id);
			ResultSet rs = psGetData.executeQuery();
			if(rs.next()){
				result = new ItemDetailsObject();
				result.setId(rs.getString(1));
				result.setShelfmark(rs.getString(2));
				result.setDescription(rs.getString(3));
				result.setNoloan(rs.getBoolean(4));
			}
			rs.close();
			psGetData.close();
			conn.close();

		} catch (Exception e) {

		}
		return result;
	}

	public String executeOrder(OrderObject order){
		return CaCaOMailOrder.execute(order);
	}
	
	public Map<Integer, IndexEntryObject> retrieveIndexSection(String cardID){
		HashMap<Integer, IndexEntryObject> map = new HashMap<Integer, IndexEntryObject>();
		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		String catalogCode = cardID.split("_")[0];
		try {
			CaCaONormalizer normalizer = CaCaONormalizerFactory.getNormalizer();
			Connection conn = CaCaOServer.getInstance().getDBConnection();
			PreparedStatement ps = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.select.indexEntrySearch"));
			ps.setString(1, catalogCode);
			ps.setString(2, cardID);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				IndexEntryObject entry = new IndexEntryObject(catalogCode, rs.getString(1), rs.getString(2), normalizer.normalize(rs.getString(2)));
				map.put(0, entry);
			}
			rs.close();
			ps.close();

			ps = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.select.indexEntryPrev"));
			ps.setString(1, catalogCode);
			ps.setString(2, cardID);
			rs = ps.executeQuery();
			int pos = 0;
			while(rs.next()){
				IndexEntryObject entry = new IndexEntryObject(catalogCode, rs.getString(1), rs.getString(2), normalizer.normalize(rs.getString(2)));
				map.put(--pos, entry);
			}
			rs.close();
			ps.close();
			
			ps = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.select.indexEntryNext"));
			ps.setString(1, catalogCode);
			ps.setString(2, cardID);
			rs = ps.executeQuery();
			pos = 0;
			while(rs.next()){
				IndexEntryObject entry = new IndexEntryObject(catalogCode, rs.getString(1), rs.getString(2), normalizer.normalize(rs.getString(2)));
				map.put(++pos, entry);
			}
			rs.close();
			ps.close();
			
			conn.close();
		} catch (Exception e) {
			//session.setAttribute(MatrikelConstants.SESSIONATTR_ERRORMSG_STRING,
			//		"Datenbank: Abrufen der Katalogdaten fehlgeschlagen: " + e.getMessage());
		}
		return map;
	}
	
	public String insertOrUpdateIndexEntry(IndexEntryObject entry){
		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		try {
			Connection conn = CaCaOServer.getInstance().getDBConnection();
			conn.setAutoCommit(false);
			PreparedStatement ps  = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.update.indexEntry"));
			ps.setString(1, entry.getText());
			ps.setString(2, CaCaONormalizerFactory.getNormalizer().normalize(entry.getText()));
			ps.setString(3, entry.getCardID());
			int rows = ps.executeUpdate();
			if(rows==0){
				//nothing UPDATED -> do an INSERT
				ps.close();
				ps  = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.insert.indexEntry"));
				ps.setString(1, entry.getCatalogCode()); 
				ps.setString(2, entry.getText());
				ps.setString(3, CaCaONormalizerFactory.getNormalizer().normalize(entry.getText()));
				ps.setString(4, entry.getCardID());
				ps.executeUpdate();
			}
			ps.close();
			conn.commit();
			conn.close();
		} catch (SQLException ex) {
			// Fehler behandeln
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			return ex.getMessage();
		} catch (Exception ex) {
			return ex.getMessage();
		}
		return null;
	}
	
	public String deleteIndexEntry(String cardID){
		Properties ipacProps = CaCaOServer.getInstance().getProperties();
		try {
			Connection conn = CaCaOServer.getInstance().getDBConnection();
			conn.setAutoCommit(false);
			PreparedStatement ps  = conn.prepareStatement(ipacProps.getProperty("CaCaO.server.db.delete.indexEntry"));
			ps.setString(1, cardID);
			ps.executeUpdate();
			ps.close();
			conn.commit();
			conn.close();
		} catch (SQLException ex) {
			// Fehler behandeln
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			return ex.getMessage();
		} catch (Exception ex) {
			return ex.getMessage();
		}
		return null;
	}
}
